﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using SilverPOP.Data.Process.Entity;
using SilverPOP.Data.Process.BLL;
using Helpers;
using Utilities;
using Atp.Net;
using SilverPOP.Library.BusinessEntities;
using System.Xml;
using System.Threading;
using SilverPOP.Library.Helpers;
using Ionic.Zip;
using LumenWorks.Framework.IO.Csv;

namespace SilverPOP.Data.Process.OperationUnit
{
    public class SilverPOPRawReportProcess : DataAndSchemaProcess
    {
        
        #region Fields
          long _totalReportTrackingData = 0;
          long _silverPOPDatabaseId = 0;
          int _rowCount = 0;
          String _sqlScriptDirectory = String.Empty;
          String _rawAnalyticsDataDirectory = String.Empty;
          DateTime _trackingStartTime, _trackingEndTime;
          List<ReportQueue> reportQueueBulkList=null;
          List<CampaignWithBlast> _activeCampaignsWithItsBlastList = new List<CampaignWithBlast>();
          DateTime _currentDateTime;
          long _lastSelectedMailingId = 0;
          string _projectName = string.Empty;
        #endregion
        
        #region Constructor
          public SilverPOPRawReportProcess(TaskListEntity task)
         {
             this.campaignId = task.CampaignId;

             DAL.DAL.SetConnectionString(task.ServerName, task.DatabaseName, task.Username, task.Password);
             _projectName = task.ProjectName;

             this.CreateFolder(ConfigHelpers.LocalDownloadDirectoryName + @"\" + _projectName + @"\Analytics-Data");
             _rawAnalyticsDataDirectory = this.directoryName ;

             _sqlScriptDirectory = this.CreateSQLReferenceFolder(ConfigHelpers.LocalDownloadDirectoryName + @"\" + _projectName + @"\SQL-Script");

         }
        #endregion 
        
        public override void ExecuteOperation()
        {
            //try
            //{
                this.processStartingTime = DateTime.Now.TimeOfDay;

                _currentDateTime = DateTime.Now;
                //Get Active Blast ID For Active Campaign ID
                _activeCampaignsWithItsBlastList = SilverPOPRawReportBLL.GetActiveCampaignWithItsBlast(this.campaignId);
                
                StartRawDataImportProcess();
                //=== Doric created two stored procedure which will update opt out and event count and update
                //=== ProspectStatus table and BlastSegment Summary table. That's why these two below methods
                //=== are not needed and commented out

                //UpdateOptOutAndBounceIndicator();
                //SetSilverPOPReportEventTypeCount();
                //==================================================

                this.CalculateTotalProcessingTime();
            //}
            //catch (Exception ex)
            //{
            //    this.LogError(ex);
            //    throw ex;
            //}
        }

        

        #region Start Raw Recipient Data Report Import Process 
        private void StartRawDataImportProcess()
        {
            _silverPOPDatabaseId = SilverPOPRawReportBLL.GetSilverPOPDatabaseIdByCampaignID(this.campaignId);

            if (_silverPOPDatabaseId > 0)
            {
               ImportSentEvent();
               ImportRawRecipientDataWithOutOptOut();
               ImportOptOutData();
            }
            else
            {
                throw new Exception("SilverPOP DatabaseId not found in database.");
            }
        }


        private void ImportSentEvent()
        {
            Console.WriteLine("Create job in SilverPOP for exporting Sent raw report data started.");
            CreateJobForExportingRawRecipientDataForSentEvent();
            Console.WriteLine("Create job in SilverPOP for exporting Sent raw report data completed.");

            DoDataDownloadingAndDataInsertion(false);
        }

        private void ImportRawRecipientDataWithOutOptOut()
        {
            Console.WriteLine("Create job in SilverPOP for exporting raw report data started.");
            CreateJobForExportingRawRecipientData(false);
            Console.WriteLine("Create job in SilverPOP for exporting raw report data has completed.");

            DoDataDownloadingAndDataInsertion(false);
        }

        private void ImportOptOutData()
        {

            Console.WriteLine("Create job in SilverPOP for exporting opt out data started.");
            CreateJobForExportingRawRecipientData(true);
            Console.WriteLine("Create job in SilverPOP for exporting opt out data has completed.");

            DoDataDownloadingAndDataInsertion(true);
        }
        #endregion

        #region Update Opt Out And Bounce Indicator In Database
        private void UpdateOptOutAndBounceIndicator()
        {
            //============ Update The Opt Out And Bounce Indicator Value in ProspectResponses Table in Database ===========
            Console.WriteLine("Updating Opt Out And Bounce Indicator in database started...");
            FileWriter.WriteToFile(this.successFileNameWithPath, "Updating Opt Out And Bounce Indicator in database started...");

            SilverPOPRawReportBLL.UpdateOptOutOrBouncedIndicatorInDatabase(_currentDateTime);

            Console.WriteLine("Updating Opt Out And Bounce Indicator in database completed.");
            FileWriter.WriteToFile(this.successFileNameWithPath, "Updating Opt Out And Bounce Indicator in database completed.");
        }
        #endregion


        #region Set Event Type Count And Unique Event Type Count For Summary

        private void SetSilverPOPReportEventTypeCount()
        {
            InsertSilverPOPReportSegmentSummmaryCount();
            InsertSilverPOPReportUniqueEventTypeCount();
        }

        private void InsertSilverPOPReportSegmentSummmaryCount()
        {
            if (reportQueueBulkList != null && reportQueueBulkList.Count > 0)
            {
                foreach (ReportQueue reportQueue in reportQueueBulkList)
                {
                    long segmentId = SilverPOPRawReportBLL.GetSegmentIDByBlastIDAndSegmentID(reportQueue.BlastId, reportQueue.MailingId);
                    SilverPOPRawReportBLL.InsertSilverPOPEventsCount(reportQueue.BlastId, reportQueue.MailingId, segmentId,_currentDateTime);
                }
            }
        }

        private void InsertSilverPOPReportUniqueEventTypeCount()
        {
            if (reportQueueBulkList != null && reportQueueBulkList.Count > 0)
            {
              //  SilverPOPAggregateReportProcess uniqueReport = new SilverPOPAggregateReportProcess(this.campaignId, true, reportQueueBulkList);
               // uniqueReport.ExecuteOperation();
            }
        }

        #endregion


        #region Report Data Downloading And Data Insertion Process
        private void DoDataDownloadingAndDataInsertion(bool isSilverPOPOptOutEvent)
        {
            Console.WriteLine("SilverPOP's reports downloading and data insertion process in Database started...");

            GetActiveReportQueueData(isSilverPOPOptOutEvent);

            Console.WriteLine("SilverPOP's reports downloading and data insertion process in Database completed...");

            Console.WriteLine("Total SilverPOP Report Tracking Data: " + _totalReportTrackingData);
            
             FileWriter.WriteToFile(this.successFileNameWithPath, "Total SilverPOP Report Tracking Data: " + _totalReportTrackingData);
        }
        #endregion

        #region Get Report Queue Data And Check JOB Status in SilverPOP Database

        private void GetActiveReportQueueData(bool isSilverPOPOptOutEvent)
        {
            if (_activeCampaignsWithItsBlastList != null)
            {
                foreach (CampaignWithBlast campaignBlast in _activeCampaignsWithItsBlastList)
                {
                    //List<long> activeBlastID = SilverPOPRawReportBLL.GetActiveBlastIDFromReportQueue(RequsetType.RawReportTracking.ToString());
                    reportQueueBulkList = new List<ReportQueue>();
                    List<ReportQueue> reportQueueList = new List<ReportQueue>();
                    int countComplete = 0;

                    if (campaignBlast.BlastIDs != null && campaignBlast.BlastIDs.Count > 0)
                    {
                        foreach (long blasID in campaignBlast.BlastIDs)
                        {
                            if (!isSilverPOPOptOutEvent)
                            {
                                reportQueueList = SilverPOPRawReportBLL.GetReportQueueByActiveBlastID(blasID, RequsetType.RawReportTracking.ToString());
                            }
                            else
                            {
                                reportQueueList = SilverPOPRawReportBLL.GetReportQueueByActiveBlastID(blasID, RequsetType.OptOut.ToString());
                            }

                            if (reportQueueList != null && reportQueueList.Count > 0)
                            {
                                reportQueueBulkList.AddRange(reportQueueList);
                            }
                        }

                        if (reportQueueBulkList != null && reportQueueBulkList.Count > 0)
                        {
                            int jobCheckingCounter = 1;

                            while (jobCheckingCounter <= ConfigHelpers.JobStatusCheckingLoopCount)
                            {
                                CheckJobStatusInSilverPOP(reportQueueBulkList);

                                countComplete = reportQueueBulkList.Where(x => x.JobsStatus == JobStatus.COMPLETE.ToString()).Count();

                                if (countComplete == reportQueueBulkList.Count)
                                    break;
                                else
                                {
                                    jobCheckingCounter++;

                                    int minutes = ConfigHelpers.JobStatusCheckingWaitingTime / 60000;
                                    Console.WriteLine("Job status checking will start again after " + minutes + " minute(s).");
                                    Thread.Sleep(ConfigHelpers.JobStatusCheckingWaitingTime);
                                    Console.WriteLine("Checking JOB Status in SilverPOP Started...");
                                }

                            }
                        }


                        if (countComplete == reportQueueBulkList.Count)
                        {
                            DownloadAndParseAnalyticsData(reportQueueBulkList,isSilverPOPOptOutEvent);
                        }
                        else
                        {
                            throw new Exception("All job status have not completed yet. So SilverPOP analytics will not run. Please try again later.");
                        }


                    }//end if active blast 
                    else
                    {
                        throw new Exception("There is no active job id found for silverpop analytics data export.");
                    }
                }//end for each loop _activeCampaignsWithItsBlastList
            }
            else
            {
                throw new Exception("There is no active campaign found.");
            }
        }

        private void DownloadAndParseAnalyticsData(List<ReportQueue> reportQueueBulkList, bool isSilverPOPOptOutEvent)
        {
            //Console.WriteLine("Please wait file generation and copying in SilverPOP is in progress...");
            //Thread.Sleep(ConfigHelpers.OverAllWaitingTime);

            List<String> downloadFTPFileNames = new List<string>();
            foreach (ReportQueue queue in reportQueueBulkList)
            {
                downloadFTPFileNames.Add(queue.FileName);
            }

            if (downloadFTPFileNames != null && downloadFTPFileNames.Count > 0)
            {
                if (DownloadFileFromFTP(downloadFTPFileNames)) //download the zip file from that ftp location to local folder
                {
                    UnzipFileAndInsertDataInDatabase(reportQueueBulkList,isSilverPOPOptOutEvent);
                }
            }

            UpdateRawReportQueueData(reportQueueBulkList);
        }

        private void UpdateRawReportQueueData(List<ReportQueue> reportQueueBulkList)
        {
            foreach (ReportQueue queue in reportQueueBulkList)
            {
                SilverPOPRawReportBLL.UpdateReportQueueData(queue.RawReportQueueId, queue.JobsStatus, DateTime.Now);
            }
        }

        private  void CheckJobStatusInSilverPOP(List<ReportQueue> reportQueueList)
        {
            if (reportQueueList != null && reportQueueList.Count > 0)
            {
                Console.WriteLine("File exporting is going to start ...");

                 foreach (ReportQueue rptQueue in reportQueueList)
                 {
                        int index = reportQueueList.IndexOf(rptQueue);

                        if (rptQueue.JobId > 0)
                        {
                            Console.WriteLine("Checking Job Status For JobID: " + rptQueue.JobId);
                            String result = SilverPopHelpers.GetJobStatus(this.InstanceSilverPOPAccount, rptQueue.JobId);

                            if (result.Contains("Success"))
                            {
                                List<String> jobStatus = result.Split(':').ToList();

                                if (jobStatus != null && jobStatus.Count > 0)
                                {
                                    Console.WriteLine("Job status is: " + jobStatus[1]);
                                    FileWriter.WriteToFile(this.successFileNameWithPath, "Job status is: " + jobStatus[1] + " and Job ID is: " + rptQueue.JobId);
                                    reportQueueList[index].JobsStatus = jobStatus[1];
                                }
                            }
                            else
                            {
                                throw new Exception(result);
                            }
                        }
                        else
                        {
                            Console.WriteLine(" JobID is Zero and row id is: " + rptQueue.RawReportQueueId);
                            FileWriter.WriteToFile(this.errorFileNameWithPath, " JobID is Zero and row id is: " + rptQueue.RawReportQueueId);
                        }

                  }
            }
            else
            {
              throw new Exception("Active Data Not Found In Report Queue");
            }
        }

        #endregion

        #region Export SilverPOP Raw Report Data And Save It In A FTP Location

        private void CreateJobForExportingRawRecipientData(bool isSilverPOPOptOutEvent)
        {
            String reportType = String.Empty;
            int optOutInsertLoopCounter = 0;

            if (_activeCampaignsWithItsBlastList != null)
            {

                //Loop Through Every Active Campaign and If Active Blast Found Then 
                //Get SilverPOP Scheduler Mailing Ids for that Blast
                foreach (CampaignWithBlast campaignBlast in _activeCampaignsWithItsBlastList)
                {
                    if (campaignBlast.BlastIDs != null && campaignBlast.BlastIDs.Count > 0)
                    {
                        foreach (long blastId in campaignBlast.BlastIDs)
                        {
                            List<ReportQueue> mailingIdList = SilverPOPRawReportBLL.GetAllActiveMailingIdByBlasId(blastId);

                            if (mailingIdList != null && mailingIdList.Count > 0)
                            {
                                Console.WriteLine("SilverPOP Job creation for raw file exporting is going to start ...");

                                //_trackingStartTime = ConfigHelpers.TrackingStartTime;
                                

                                foreach (ReportQueue rptQueue in mailingIdList)
                                {
                                    if (isSilverPOPOptOutEvent)
                                        _trackingStartTime = SilverPOPRawReportBLL.GetLastDateForRawProspectTracking(this.campaignId, rptQueue.MailingId, "Opt Out");
                                    else
                                        _trackingStartTime = SilverPOPRawReportBLL.GetLastDateForRawProspectTracking(this.campaignId, rptQueue.MailingId, "Others");

                                    _trackingEndTime = ConfigHelpers.TrackingEndTime;

                                    bool isExist = false;
                                    string optOutMarkText = String.Empty;
                                   

                                    // === Check for repeated MailingID  ===
                                    // === If repeated MailingID found then not execute for it
                                    if (_lastSelectedMailingId <= 0 || _lastSelectedMailingId != rptQueue.MailingId || mailingIdList.Count == 1)
                                    {
                                        _lastSelectedMailingId = rptQueue.MailingId;
                                    }
                                    else if (_lastSelectedMailingId == rptQueue.MailingId)
                                    {
                                        continue;
                                    }
                                    //==============================================

                                    if (!isSilverPOPOptOutEvent)
                                    {
                                        //====If this is not opt out event then execute this block ====

                                        reportType = RequsetType.RawReportTracking.ToString();
                                        isExist = SilverPOPRawReportBLL.CheckActiveReportQueseDataExist(rptQueue.BlastId, rptQueue.MailingId,reportType );
                                    }
                                    else
                                    {
                                        //====If this is opt out event then execute this block ===

                                        reportType = RequsetType.OptOut.ToString();
                                        isExist = SilverPOPRawReportBLL.CheckActiveReportQueseDataExist(rptQueue.BlastId, rptQueue.MailingId, reportType);
                                        optOutMarkText = "OptOut-";
                                    }

                                    if (isExist)
                                        continue;

                                    String fileName = rptQueue.MailingId + "-Export-" + optOutMarkText + String.Format("{0:d}", DateTime.Now).Replace("/", "");
                                    String result = String.Empty;

                                    if (!isSilverPOPOptOutEvent)
                                    {
                                        //====If this is not opt out event then execute this block ===

                                        result = SilverPopHelpers.RawRecipientDataExport(this.InstanceSilverPOPAccount, rptQueue.MailingId, fileName, _trackingStartTime, _trackingEndTime);
                                        
                                    }
                                    else
                                    {
                                        //====If this is opt out event then execute this block ===

                                        if (rptQueue.ContactListId != null)
                                        {
                                            //=== If contact list exist then get opt out data using contact list id ========
                                            result = SilverPopHelpers.ExportList(this.InstanceSilverPOPAccount, rptQueue.ContactListId.Value, _trackingStartTime, _trackingEndTime);
                                        }
                                        else
                                        {
                                            //=== If contact list not exist then get opt out data using silverpop database id ========

                                            //For Opt Out We Get Data Using DatabaseID, So it will execute only one time
                                            if (optOutInsertLoopCounter < 1)
                                            {
                                                result = SilverPopHelpers.ExportList(this.InstanceSilverPOPAccount, _silverPOPDatabaseId, _trackingStartTime, _trackingEndTime);
                                                optOutInsertLoopCounter++;
                                            }
                                            else
                                            {
                                                optOutInsertLoopCounter++;
                                            }
                                        }
                                        result = result.Contains("Success") ? result.Replace("/download/", string.Empty) : result;
                                    }


                                    Console.WriteLine("Job is created for raw exporting ...");
                                    DataJobInsertionInDB(result, fileName, rptQueue, campaignBlast, reportType, isSilverPOPOptOutEvent, optOutInsertLoopCounter);
                                    


                                }//end foreach loop for ReportQueue

                            }//end if mailingIdList != null

                        }//end foreach loop for blastId

                    }//end if campaignBlast.BlastIDs != null
                }//end foreach loop Campaign With Blast

            }//end if _activeCampaignsWithItsBlastList is not null
            else
            {
                throw new Exception("There is no active campaign found.");
            }
        }

        private void DataJobInsertionInDB(String result, String fileName, ReportQueue rptQueue, CampaignWithBlast campaignBlast, String reportType, bool isSilverPOPOptOutEvent,int insertCounter)
        {
            //For Opt Out We Get Data Using DatabaseID, So it will execute only one time
            if (isSilverPOPOptOutEvent && insertCounter > 1)
                return;
                
            
            
            if (result.Contains("Success"))
             {
                 List<String> fileNameWithJobList = result.Split(':').ToList();
                 if (fileNameWithJobList != null && fileNameWithJobList.Count > 0)
                 {
                     Console.WriteLine("Exporting file name: " + fileName + " and job id is: " + fileNameWithJobList[2]);
                     FileWriter.WriteToFile(this.successFileNameWithPath, "Exporting file name: " + fileName + " and job id is: " + fileNameWithJobList[2]);
                    InsertDataInReportQueueTableForProspectTracking(fileNameWithJobList[1], fileNameWithJobList[2], fileNameWithJobList[3], rptQueue.BlastId, rptQueue.MailingId, campaignBlast.Campaign.CampaignID,rptQueue.ContactListId,reportType);
                 }
            }
            else
            {
              FileWriter.WriteToFile(this.errorFileNameWithPath, result);
            }

        }

        private void CreateJobForExportingRawRecipientDataForSentEvent()
        {
            String reportType = String.Empty;
            if (_activeCampaignsWithItsBlastList != null)
            {

                //Loop Through Every Active Campaign and If Active Blast Found Then 
                //Get SilverPOP Scheduler Mailing Ids for that Blast
                foreach (CampaignWithBlast campaignBlast in _activeCampaignsWithItsBlastList)
                {
                    if (campaignBlast.BlastIDs != null && campaignBlast.BlastIDs.Count > 0)
                    {
                        foreach (long blastId in campaignBlast.BlastIDs)
                        {
                            List<ReportQueue> mailingIdList = SilverPOPRawReportBLL.GetAllActiveMailingIdByBlasId(blastId);

                            if (mailingIdList != null && mailingIdList.Count > 0)
                            {


                                Console.WriteLine("SilverPOP Job creation for raw file for sent report  is going to start ...");

                                //_trackingStartTime = ConfigHelpers.TrackingStartTime;
                                

                                foreach (ReportQueue rptQueue in mailingIdList)
                                {
                                    _trackingStartTime = SilverPOPRawReportBLL.GetLastDateForRawProspectTracking(this.campaignId, rptQueue.MailingId, "Sent");
                                    _trackingEndTime = ConfigHelpers.TrackingEndTime;

                                    bool isExist = false;

                                    // === Check for repeated MailingID  ===
                                    // === If repeated MailingID found then not execute for it
                                    if (_lastSelectedMailingId <= 0 || _lastSelectedMailingId != rptQueue.MailingId || mailingIdList.Count == 1)
                                    {
                                        _lastSelectedMailingId = rptQueue.MailingId;
                                    }
                                    else if (_lastSelectedMailingId == rptQueue.MailingId)
                                    {
                                        continue;
                                    }
                                    //==============================================

                                    // ===Sent Event Data Already Inserted For This Mailing ID. So we do not need to insert again
                                    bool isInserted = SilverPOPRawReportBLL.IsSentEventInsertedInDatabase(this.campaignId, _lastSelectedMailingId);

                                    if (isInserted)
                                        continue;
                                    //=========================================

                                    reportType = RequsetType.RawReportTracking.ToString();
                                    isExist = SilverPOPRawReportBLL.CheckActiveReportQueseDataExist(rptQueue.BlastId, rptQueue.MailingId, reportType);



                                    if (isExist)
                                        continue;

                                    String fileName = rptQueue.MailingId + "-Export-" + "-Sent-" + String.Format("{0:d}", DateTime.Now).Replace("/", "");
                                    String result = String.Empty;

                                    result = SilverPopHelpers.RawRecipientSentDataExport(this.InstanceSilverPOPAccount, rptQueue.MailingId, fileName, _trackingStartTime, _trackingEndTime);


                                    Console.WriteLine("Job is created for sent raw exporting ...");

                                    if (result.Contains("Success"))
                                    {
                                        List<String> fileNameWithJobList = result.Split(':').ToList();

                                        if (fileNameWithJobList != null && fileNameWithJobList.Count > 0)
                                        {
                                            Console.WriteLine("Exporting file name: " + fileName + " and job id is: " + fileNameWithJobList[2]);
                                            FileWriter.WriteToFile(this.successFileNameWithPath, "Exporting file name: " + fileName + " and job id is: " + fileNameWithJobList[2]);
                                            InsertDataInReportQueueTableForProspectTracking(fileNameWithJobList[1], fileNameWithJobList[2], fileNameWithJobList[3], rptQueue.BlastId, rptQueue.MailingId, campaignBlast.Campaign.CampaignID, rptQueue.ContactListId, reportType);
                                        }
                                    }
                                    else
                                    {
                                        FileWriter.WriteToFile(this.errorFileNameWithPath, result);
                                    }


                                }//end foreach loop for ReportQueue

                            }//end if mailingIdList != null

                        }//end foreach loop for blastId

                    }//end if campaignBlast.BlastIDs != null
                }//end foreach loop Campaign With Blast

            }//end if _activeCampaignsWithItsBlastList is not null
            else
            {
                throw new Exception("There is no active campaign found.");
            }
        }

        private   void InsertDataInReportQueueTableForProspectTracking(String fileName, String jobID, String jobStatus, long blastID, long mailingID,long campaignID,long? contactListId,String reportType)
        {
            long silverPOPJobId = Convert.ToInt64(jobID);
            long rowInserted = SilverPOPRawReportBLL.InsertReportDataInReportQueueForProspectTracking(fileName, silverPOPJobId, jobStatus, blastID, mailingID, DateTime.Now, reportType, campaignID, _currentDateTime, contactListId);

        }



        #endregion

        #region Download The Zipped Raw Report File From FTP Location To Local Folder
        private bool DownloadFileFromFTP(List<String> downloadFTPFileNames)
        {
            Console.WriteLine("Conecting to FTP...");
             FileWriter.WriteToFile(this.successFileNameWithPath, "Conecting to FTP...");

            String remoteLocation = String.Empty;
            String localLocation = ConfigHelpers.LocalDownloadDirectoryName + @"\" + _projectName + @"\Analytics-Data\" + folderName;

            Sftp client = new Sftp();
            client.Connect(ConfigHelpers.FTPHost, ConfigHelpers.FTPPort);
            client.Authenticate(ConfigHelpers.FTPUserName, ConfigHelpers.FTPPassword);


            if (client.IsConnected)
            {
                Console.WriteLine("Successfully connected to FTP.");
                 FileWriter.WriteToFile(this.successFileNameWithPath, "Successfully connected to FTP.");

                client.KeepAlive();
                String defaultDir = client.GetCurrentDirectory();
                defaultDir += ConfigHelpers.FTPDownloadDirectoryName;

                Console.WriteLine("File downloading is going to start...");

                foreach (String downloadingFileName in downloadFTPFileNames)
                {

                    String localFileNameWithPath = localLocation + @"\" + downloadingFileName;
                    String remoteFileNameWithPath = defaultDir + @"/" + downloadingFileName;

                    if (!Directory.Exists(localLocation))
                    {
                        Directory.CreateDirectory(localLocation);
                    }

                    if (client.FileExists(remoteFileNameWithPath))
                    {
                        long identifier = client.DownloadFile(remoteFileNameWithPath, localFileNameWithPath);
                        Console.WriteLine("File downloaded successfully. File name is: " + downloadingFileName);
                    }
                    else
                    {
                        Console.WriteLine("File not found in remote FTP. File name is: " + downloadingFileName);
                         FileWriter.WriteToFile(this.errorFileNameWithPath, "File not found in remote FTP. File name is: " + downloadingFileName);
                    }

                }

                client.KeepAlive();

            }
            Console.WriteLine("File downloading completed.");
            return true;
        }
        #endregion

        #region UnZipped The Report Files

        private void UnzipFileAndInsertDataInDatabase(List<ReportQueue> reportQueueList, bool isSilverPOPOptOutEvent)
        {
            //String directoryPath = ConfigHelpers.LocalDownloadDirectoryName + @"\Analytics-Data\" + folderName;

            if (UnZipFile(this.directoryName, reportQueueList.Where(x => x.MailingId != -1).Select(x => x.FileName).ToList())) // if unzip successfull then parse data from csv file
            {
                ParseCSVDataAndCreateSQLInsertStatments(reportQueueList, this.directoryName,isSilverPOPOptOutEvent);
            }
        }


        private bool UnZipFile(String unzipDirectoryPath, List<String> downloadFTPFileNames)
        {

            Console.WriteLine("File UnZipping started ...");

            DirectoryInfo di = new DirectoryInfo(unzipDirectoryPath);

            foreach (FileInfo fi in di.GetFiles("*.zip"))
            {
                if (!String.IsNullOrEmpty(downloadFTPFileNames.Find(x => x.ToLower() == fi.Name.Trim().ToLower())))
                {
                    using (ZipFile zip = new ZipFile())
                    {
                        string zipToExtract = fi.FullName;
                        string unzipDirectory = unzipDirectoryPath;

                        using (ZipFile zipFile = ZipFile.Read(zipToExtract))
                        {
                            foreach (ZipEntry extractFile in zipFile)
                            {
                                Console.WriteLine("UnZipped file: " + fi.FullName);

                                extractFile.Extract(unzipDirectory, ExtractExistingFileAction.OverwriteSilently);
                            }
                        }
                    }
                }
            }

            Console.WriteLine("File UnZipping completed ...");

            return true;
        }

        #endregion

        #region Read/Parse Extracted Data From CSV File and Insert Into Database Table

        private void ParseCSVDataAndCreateSQLInsertStatments(List<ReportQueue> reportQueueList, String unzipDirectoryPaths, bool isSilverPOPOptOutEvent)
        {
            //ParseCSVDataAndCreateSQLInsertStatments(reportQueueList.Where(x => x.ContactListId == -1).Select(x => x.FileName).ToList(), this.directoryName);

            string xmlData = String.Empty;
            SilverPOPReports reports = new SilverPOPReports();
            //bool isTruncateTable = true;
            StringBuilder allSqlStatements = new StringBuilder();
            StringBuilder sqlStatements = new StringBuilder();
            long totalcounter = 0;
            long csvfileRowCount = 0;


            foreach (ReportQueue repQueue in reportQueueList)
            {
                if (repQueue.MailingId > 0)
                {
                    String csvFileName = repQueue.FileName.Replace(".zip", ".csv");
                    sqlStatements = null;

                    if (File.Exists(unzipDirectoryPaths + @"\" + csvFileName))
                    {
                        CsvReader csv = new CsvReader(new StreamReader(unzipDirectoryPaths + @"\" + csvFileName), true);
                        csv.MissingFieldAction = MissingFieldAction.ReplaceByNull;

                        int fieldCount = csv.FieldCount;
                        int rowRangeCounter = 1;
                        _rowCount = 1;
                        csvfileRowCount = 0;

                        FileWriter.WriteToFile(this.successFileNameWithPath, "Parsing File: " + csvFileName);

                        while (csv.ReadNextRecord())
                        {

                            _totalReportTrackingData++;

                            reports = new SilverPOPReports();

                            if (rowRangeCounter == 1)
                            {
                                sqlStatements = new StringBuilder();
                            }
                            try
                            {
                                //if (ParsingHelpers.CleanString(csv["Event Type"]) == "Opt Out")
                                    //continue;
                                String strProspectId=String.Empty;
                                reports.ProspectId = 0;
                                reports.CampaignId = repQueue.CampaignId;
                                reports.DateCreated = _currentDateTime;

                                if (!isSilverPOPOptOutEvent)
                                {
                                    //====If this is not opt out event then execute this block ====
                                   
                                    reports.RecipientId = ParsingHelpers.CleanString(csv["Recipient Id"]);
                                    reports.RecipientType = ParsingHelpers.CleanString(csv["Recipient Type"]);
                                    reports.MailingId = ParsingHelpers.CleanString(csv["Mailing Id"]);
                                    reports.ReportId = ParsingHelpers.CleanString(csv["Report Id"]);
                                    reports.SilverPOPCampaignId = ParsingHelpers.CleanString(csv["Campaign Id"]);
                                    reports.Email = ParsingHelpers.ReplaceString(ParsingHelpers.CleanString(csv["Email"]), "'", String.Empty);
                                    reports.EventType = ParsingHelpers.CleanString(csv["Event Type"]);
                                    reports.EventTimestamp = ParsingHelpers.CleanString(csv["Event Timestamp"]);
                                    reports.LinkName = ParsingHelpers.CleanString(csv["Click Name"]);
                                    reports.LinkUrl = ParsingHelpers.CleanString(csv["URL"]);
                                    strProspectId = ParsingHelpers.CleanString(csv["ProspectId"]);
                                }
                                else
                                {
                                    //====If this is opt out event then execute this block ====

                                    reports.MailingId = repQueue.MailingId.ToString();
                                    reports.Email = ParsingHelpers.ReplaceString(ParsingHelpers.CleanString(csv["Email"]), "'", String.Empty);
                                    reports.EventType = "Opt Out";
                                    reports.EventTimestamp = ParsingHelpers.CleanString(csv["Opted Out Date"]);
                                    strProspectId = ParsingHelpers.CleanString(csv["ProspectId"]);
                                }
                                
                                long prospectId = 0;
                                bool isCastSuccess = long.TryParse(strProspectId, out prospectId);
                                reports.ProspectId = isCastSuccess ? prospectId :0;
                                
                             
                                
                            }
                            catch (Exception ex)
                            {
                                String rowCounter = "Row no: " + _rowCount + ". ";
                                this.LogError(ex, rowCounter);
                            }

                            #region Now we do not truncate table for every report sync. We add new  row
                            //==============================================================================
                            //if (isTruncateTable)
                            //{
                            //if (ConfigHelpers.IsRunningFirstTime)
                            //{
                            //sqlStatements.AppendLine(" TRUNCATE TABLE RawProspectTracking;");
                            //}

                            //isTruncateTable = false;
                            //}
                            #endregion

                            GenerateInsertSQLStatements(reports, sqlStatements);

                            if (rowRangeCounter == 1000)
                            {
                                //Keep the SQL Script file for reference
                                allSqlStatements.Append(sqlStatements.ToString());

                                Console.WriteLine("Data insertion started ....  ");

                                SilverPOPRawReportBLL.InsertSilverPOPRawReportsDataBySQLScript(sqlStatements.ToString());

                                Console.WriteLine("Data insertion completed ....  ");

                                //Reset Values
                                rowRangeCounter = 1;
                                //isTruncateTable = false;
                                sqlStatements = null;
                            }
                            else
                            {
                                rowRangeCounter++;
                            }
                            Console.WriteLine("SilverPOP  row counter: " + _rowCount);

                            _rowCount++;

                            totalcounter++;

                            csvfileRowCount++;

                        }//end while

                        FileWriter.WriteToFile(this.successFileNameWithPath, "Total row in csv file " + csvFileName + " : " + csvfileRowCount);

                        if (sqlStatements != null && !String.IsNullOrEmpty(sqlStatements.ToString()))
                        {

                            //Keep the SQL Script file for reference
                            allSqlStatements.Append(sqlStatements.ToString());

                            Console.WriteLine("Data insertion started ....  ");

                            SilverPOPRawReportBLL.InsertSilverPOPRawReportsDataBySQLScript(sqlStatements.ToString());

                            Console.WriteLine("Data insertion completed ....  ");
                        }

                        Console.WriteLine("Total row imported : " + totalcounter);
                        _totalReportTrackingData = totalcounter;


                    }// end if file exist

                }//end if repQueue.ContactListId == -1

            }//end foreach loop

            if (!String.IsNullOrEmpty(allSqlStatements.ToString()))
            {
                File.WriteAllText(_sqlScriptDirectory + @"\" + "SQL-File" + "_" + _rowCount.ToString() + ".sql", allSqlStatements.ToString());
            }
        }



        private   void GenerateInsertSQLStatements(SilverPOPReports reports, StringBuilder sqlStatements)
        {

            sqlStatements.AppendLine("EXEC SPR_Insert_Data_In_RawProspectTracking "
                                                  + ParsingHelpers.AppendWithSingleQuote(reports.RecipientId) + ","
                                                  + ParsingHelpers.AppendWithSingleQuote(reports.RecipientType) + ","
                                                  + ParsingHelpers.AppendWithSingleQuote(reports.MailingId) + ","
                                                  + ParsingHelpers.AppendWithSingleQuote(reports.ReportId) + ","
                                                  + ParsingHelpers.AppendWithSingleQuote(reports.SilverPOPCampaignId) + ","
                                                  + ParsingHelpers.AppendWithSingleQuote(ParsingHelpers.ReplaceString(reports.Email, "'", String.Empty)) + ","
                                                  + ParsingHelpers.AppendWithSingleQuote(reports.EventType) + ","
                                                  + ParsingHelpers.AppendWithSingleQuote(reports.EventTimestamp) + ","
                                                  + ParsingHelpers.AppendWithSingleQuote(reports.LinkName) + ","
                                                  + ParsingHelpers.AppendWithSingleQuote(reports.LinkUrl) + ","
                                                  + (reports.ProspectId == 0 ? "NULL" : Convert.ToString(reports.ProspectId)) + ","
                                                  + reports.CampaignId + ","
                                                  + ParsingHelpers.AppendWithSingleQuote(reports.DateCreated.ToString())
                                                 //+ ParsingHelpers.AppendWithSingleQuote(DateTime.Now.ToString())
                                                  + ";"

                                    );
        }


        #endregion
    
    
    }//end class
}
